package com.bluefeather.songlyric;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;

import android.content.ContentValues;
import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.preference.PreferenceManager;
import android.provider.BaseColumns;
import android.util.Log;

public class DB extends SQLiteOpenHelper {
	
	static private String 			TAG			= "DB";
	static private DB				_instance	= null;
	static private final int		_version 	= 21;
	static private boolean			_isEmpty	= false;
	static private final int		LIST_LIMIT	= 10;
	static private final String		LIST_LIMIT_S = "10";
	static public final int			AUTHOR_FAMOUS = 1;
	static public final int			AUTHOR_BOTH	= 2;
	static public final int			AUTHOR_SELF	= 3;
	
	// MUSIC LYRIC tables

	public static abstract class MUSIC implements BaseColumns {		// 词牌
		public static final String _T		= "MUSIC";
		public static final String _NAME	= "_name";
		public static final String _WORDS	= "_words";	// 字数
		public static final String _LINES1	= "_lines1";// 上阕行数
		public static final String _LINES2	= "_lines2";// 下阕行数, can be 0
		public static final String _SIZE	= "_size";	// s|m|l
		public static final String _TONE_TYPE	= "_toneType";	// 平仄 p=1|z=2|c=3
		public static final String _FAVORIT	= "_favorit";
	}
	
	public static abstract class MUSICLINE implements BaseColumns {	// 平仄表
		public static final String _T		= "MUSICLINE";
		public static final String _MUSIC	= "_music";
		public static final String _LINE	= "_line";	// 行
		// _words: each character in words represents a tone
		// possible values: 平仄中云芸韵运
		public static final String _WORDS	= "_words";
	}
	
	public static abstract class LYRIC implements BaseColumns {		// 词
		public static final String _T		= "LYRIC";
		public static final String _MUSIC	= "_music";
		public static final String _DYNASTY	= "_dynasty";	// 朝代
		public static final String _AUTHOR	= "_author";	// 作者
		public static final String _TITLE	= "_title";		// 标题
		public static final String _TONE	= "_tone";		// 韵部
		public static final String _FAVORIT	= "_favorit";
	}
	
	public static abstract class LYRICLINE implements BaseColumns {	// 行
		public static final String _T		= "LYRICLINE";
		public static final String _LYRIC	= "_lyric";
		public static final String _LINE	= "_line";
		public static final String _WORDS	= "_words";
	}
	
	static public DB instance() {
		if(_instance == null) {
			LogDog.i(TAG, "creating new DB instance");
			_instance = new DB(SongLyricApp.instance());
			_instance.getReadableDatabase(); // which will call CreateDatabase
			// import music list
			if(_isEmpty) {
				_instance.importList();
				_isEmpty = false;
			}
		}
		return _instance;
	}
	
	private DB(Context context) {
		super(context, TAG, null, _version);
	}
	
	public int countMusics() {
		Cursor c = getReadableDatabase().query(MUSIC._T, new String[] {MUSIC._ID},
			null, null, null, null, null);
		return c.getCount();
	}
	
	public int countMusicPages(String where) {
		Cursor c = getReadableDatabase().query(MUSIC._T, new String[] {MUSIC._ID},
			where, null, null, null, null);
		return (c.getCount() + LIST_LIMIT - 1) / LIST_LIMIT;
	}
	
	public Cursor getMusicList(String where, int firstMusic) {
		String myWhere = MUSIC._ID + " > " + firstMusic;
		String[] cols = new String[] { MUSIC._ID, MUSIC._NAME };
		Cursor c = getReadableDatabase().query(MUSIC._T, cols,
			where == null ? myWhere : where + " AND " + myWhere,
			null, null, null, MUSIC._ID, LIST_LIMIT_S);
		c.moveToFirst();
		return c;
	}
	
	public Cursor getMusic(int musicId) {
		String[] cols = new String[] { MUSIC._NAME, MUSIC._LINES1, MUSIC._LINES2, MUSIC._WORDS };
		Cursor c = getReadableDatabase().query(MUSIC._T, cols, MUSIC._ID + "=" + musicId, null, null, null, null);
		c.moveToFirst();
		return c;
	}
	
	public Cursor getMusicLines(int musicId) {
		String[] cols = new String[] { MUSICLINE._LINE, MUSICLINE._WORDS };
		Cursor c = getReadableDatabase().query(MUSICLINE._T, cols, MUSICLINE._MUSIC + "=" + musicId,
			null, null, null, MUSICLINE._LINE);
		c.moveToFirst();
		return c;
	}
	
	public Cursor getAuthorList() {
		SharedPreferences sp = PreferenceManager.getDefaultSharedPreferences(SongLyricApp.instance());
		int authorMode = sp.getInt("AUTHOR_MODE", AUTHOR_FAMOUS);
		String author = SongLyricApp.instance().getResources().getString(R.string.app_author);
		String where = null;
		if(authorMode == AUTHOR_FAMOUS)
			where = new String(LYRIC._AUTHOR + " <> '" + author + "'");
		else if(authorMode == AUTHOR_SELF)
			where = new String(LYRIC._AUTHOR + " = '" + author + "'");
		String[] cols = new String[] { LYRIC._AUTHOR, LYRIC._DYNASTY };
		Cursor c = getReadableDatabase().query(LYRIC._T, cols, where, null,
			LYRIC._AUTHOR + ", " + LYRIC._DYNASTY, null, " COUNT(*) DESC ");
		c.moveToFirst();
		return c;
	}
	
	public Cursor getLyricList(String where) {
		SharedPreferences sp = PreferenceManager.getDefaultSharedPreferences(SongLyricApp.instance());
		int authorMode = sp.getInt("AUTHOR_MODE", AUTHOR_FAMOUS);
		StringBuilder sb = new StringBuilder();
		sb.append(where);
		String author = SongLyricApp.instance().getResources().getString(R.string.app_author);
		if(authorMode == AUTHOR_FAMOUS)
			sb.append(" AND " + LYRIC._AUTHOR + " <> '" + author + "'");
		else if(authorMode == AUTHOR_SELF)
			sb.append(" AND " + LYRIC._AUTHOR + " = '" + author + "'");
		String[] cols = new String[] {
			LYRIC._ID, LYRIC._TITLE, LYRIC._DYNASTY, LYRIC._AUTHOR, LYRIC._MUSIC };
		Cursor c = getReadableDatabase().query(LYRIC._T, cols, sb.toString(), null, null, null, null);
		c.moveToFirst();
		return c;
	}
	
	public Cursor getLyricList(int musicId) {
		String where = LYRIC._MUSIC + "=" + musicId;
		return getLyricList(where);
	}
	
	public Cursor getLyricLines(int lyricId) {
		String[] cols = new String[] { LYRICLINE._LINE, LYRICLINE._WORDS };
		Cursor c = getReadableDatabase().query(LYRICLINE._T, cols, LYRICLINE._LYRIC + "=" + lyricId,
			null, null, null, null);
		c.moveToFirst();
		return c;
	}
	
	public void bookmarkMusic(int musicId) {
		Cursor c = getReadableDatabase().query(MUSIC._T, new String[] { "MAX(" + MUSIC._FAVORIT +")" },
			null, null, null, null, null);
		c.moveToFirst();
		int maxValue = c.getInt(0) + 1;
		c.close();
		ContentValues cv = new ContentValues();
		cv.put(MUSIC._FAVORIT, maxValue);
		getWritableDatabase().update(MUSIC._T, cv, MUSIC._ID + "=" + musicId, null);
		LogDog.i(TAG, "updated favorite to " + maxValue + " for music " + musicId);
	}
	
	public void deleteLyric(int lyricId) {
		getWritableDatabase().delete(LYRIC._T, LYRIC._ID + "=" + lyricId, null);
		LogDog.i(TAG, "delete lyric " + lyricId);
	}

	private void importLyrics(int musicId, String name) {
		try {
			InputStream is = SongLyricApp.instance().getAssets().open("" + musicId + ".lyric");
			BufferedReader reader = new BufferedReader(new InputStreamReader(is));
			String s;
			int line = 0;
			String title = "";
			String dynasty = "";
			String author = "";
			int lyricId = 0;
			int toneId = 0;
			String titleHint = name + ":";
			SQLiteDatabase db = getWritableDatabase();
			while((s = reader.readLine()) != null) {
				if(s.startsWith("#") || s.isEmpty()) {
					if(s.startsWith("#TONE:"))
						toneId = Integer.valueOf(s.substring(6));
					continue;
				}
				if(s.startsWith(titleHint)) { // start of a lyric
					lyricId = 0;
					line = 0;
					title = s.substring(titleHint.length()).trim();
					continue;
				}
				if(s.indexOf(" ") > 0) { // dynasty + space + author
					int pos = s.indexOf(" ");
					dynasty = s.substring(0, pos);
					author = s.substring(pos + 1);
					continue;
				}
				if(lyricId == 0) {		// new lyric
					ContentValues cvLyric = new ContentValues();
					cvLyric.put(LYRIC._MUSIC, musicId);
					cvLyric.put(LYRIC._TITLE, title);
					cvLyric.put(LYRIC._DYNASTY, dynasty);
					cvLyric.put(LYRIC._AUTHOR, author);
					cvLyric.put(LYRIC._TONE, toneId);
					lyricId = (int) db.insert(LYRIC._T, null, cvLyric);
					LogDog.i(TAG, "new lyric " + title + " by " + author + ":" + lyricId);
					toneId = 0;	// oops I should make the line after titleHint... but no time to change
				}
				ContentValues cv = new ContentValues();
				cv.put(LYRICLINE._LYRIC, lyricId);
				cv.put(LYRICLINE._LINE, line++);
				cv.put(LYRICLINE._WORDS, s);
				db.insert(LYRICLINE._T, null, cv);
			}
			reader.close();
		} catch (IOException e) {
			LogDog.e(TAG, "Failed to import lyric " + musicId + " because " + e.getMessage());
		}
	}
	
	public void importMusic(int musicId) {
		Cursor c = getMusicLines(musicId);
		if(c.getCount() > 0) // already there
			return;
		Cursor c1 = getMusic(musicId);
		String name = c1.getString(c1.getColumnIndex(MUSIC._NAME));
		c1.close();
		LogDog.i(TAG, "importing music " + musicId + ":" + name);
		try {
			InputStream is = SongLyricApp.instance().getAssets().open("" + musicId + ".music");
			BufferedReader reader = new BufferedReader(new InputStreamReader(is));
			String s;
			int lines1 = 0;
			int lines2 = 0;
			int line = 0;
			int totalWords = 0;
			SQLiteDatabase db = getWritableDatabase();
			while((s = reader.readLine()) != null) {
				if(s.startsWith("#"))
					continue;
				if(s.isEmpty()) {
					if(line > 0 && lines1 == 0)	// we already have lines, so this is start of another block
						lines1 = line;
					continue;
				}
				ContentValues cv = new ContentValues();
				cv.put(MUSICLINE._MUSIC, musicId);
				cv.put(MUSICLINE._LINE, line);
				cv.put(MUSICLINE._WORDS, s);
				db.insert(MUSICLINE._T, null, cv);
				line++;
				totalWords += s.length();
			}
			if(lines1 > 0)
				lines2 = line - lines1;
			else
				lines1 = line;
			ContentValues cv = new ContentValues();
			cv.put(MUSIC._LINES1, lines1);
			cv.put(MUSIC._LINES2, lines2);
			cv.put(MUSIC._WORDS, totalWords);
			db.update(MUSIC._T, cv, MUSIC._ID + "=" + musicId, null);
			reader.close();
		} catch (IOException e) {
			LogDog.e(TAG, "Failed to import music " + musicId + " because " + e.getMessage());
		}
		LogDog.i(TAG, "importing lyric " + musicId + ":" + name);
		importLyrics(musicId, name);
	}
	
	private void importList() {
		LogDog.i(TAG, "importList");
		try {
			InputStream is = SongLyricApp.instance().getAssets().open("musiclist.csv");
			BufferedReader reader = new BufferedReader(new InputStreamReader(is));
			String s;
			SQLiteDatabase db = getWritableDatabase();
			while((s = reader.readLine()) != null) {
				if(s.startsWith("#"))
					continue;
				String[] values = s.split(",");
				if(values.length != 4)
					LogDog.e(TAG, "bad line " + s);
				int id = Integer.parseInt(values[0]);
				LogDog.i(TAG, "MUSIC " + id + ":" + values[1]);
				ContentValues cv = new ContentValues();
				cv.put(MUSIC._ID, id);
				cv.put(MUSIC._NAME, values[1]);
				cv.put(MUSIC._SIZE, values[2]);
				int toneType = values[3].equals("p") ? TONE_TYPE_P :
					values[3].equals("z") ? TONE_TYPE_Z :
					values[3].equals("c") ? TONE_TYPE_C : 0;
				cv.put(MUSIC._TONE_TYPE, toneType);
				db.insert(MUSIC._T, null, cv);
			}
			reader.close();
		} catch (IOException e) {
			LogDog.e(TAG, "Failed to import musiclist.csv because " + e.getMessage());
		}
	}
	
	// TONE WORD tables
	
	public static final int TONE_TYPE_P		= 1;
	public static final int TONE_TYPE_Z		= 2;
	public static final int TONE_TYPE_R		= 3;
	public static final int TONE_TYPE_C		= 4;
	
	public static abstract class TONE implements BaseColumns {
		public static final String _T		= "TONE";
		public static final String _INDEX	= "_index";
		public static final String _TITLE	= "_title";
		public static final String _TYPE	= "_type";
		public static final String _SUBJECT	= "_subject";
	};
	
	public static abstract class TONEWORD implements BaseColumns {
		public static final String _T		= "TONEWORD";
		public static final String _INDEX	= "_index";
		public static final String _TYPE	= "_type";
		public static final String _SUBJECT = "_subject";
		public static final String _WORDS	= "_words";
	};
	
	public Cursor findToneWord(String ch) {
		String[] cols = new String[] { TONEWORD._INDEX, TONEWORD._TYPE, TONEWORD._WORDS };
		String where = TONEWORD._WORDS + " like '%" + ch + "%'";
		Cursor c = getReadableDatabase().query(TONEWORD._T, cols, where, null, null, null, null);
		c.moveToFirst();
		return c;
	}
	
	public int getToneIndex(int toneId) {
		String[] cols = new String[] { TONE._INDEX };
		Cursor c = getReadableDatabase().query(TONE._T, cols, TONE._ID + "=" + toneId, null, null, null, null);
		if(c.getCount() <= 0)
			return 0;
		c.moveToFirst();
		return c.getInt(c.getColumnIndex(TONE._INDEX));
	}
	
	public String getToneTitle(int toneIndex) {
		String[] cols = new String[] { TONE._TITLE };
		Cursor c = getReadableDatabase().query(TONE._T, cols, TONE._INDEX + "=" + toneIndex, null, null, null, null);
		if(c.getCount() <= 0)
			return null;
		c.moveToFirst();
		return c.getString(c.getColumnIndex(TONE._TITLE));
	}
	
	public Cursor getToneList(int type) {
		String[] cols = new String[] { TONE._ID, TONE._TITLE, TONE._SUBJECT };
		String where = TONE._TYPE + "=" + type;
		if(type == TONE_TYPE_Z)
			where += " OR " + TONE._TYPE + "=" + TONE_TYPE_R;
		LogDog.i(TAG, "getToneList for " + type + ":" + where);
		Cursor c = getReadableDatabase().query(TONE._T, cols, where, null, null, null, null);
		if(c.getCount() > 0)
		{
			c.moveToFirst();
			return c;
		}
		importTone();
		c = getReadableDatabase().query(TONE._T, cols, where, null, null, null, null);
		c.moveToFirst();
		return c;
	}
	
	public Cursor getToneWords(int index, int type) {
		String[] cols = new String[] { TONEWORD._ID, TONEWORD._SUBJECT, TONEWORD._WORDS };
		String where = "(";
		where += TONEWORD._TYPE + "=" + type;
		if(type == TONE_TYPE_Z)
			where += " OR " + TONEWORD._TYPE + "=" + TONE_TYPE_R;
		where += ")";
		Cursor c = getReadableDatabase().query(TONEWORD._T, cols,
			where + " AND " + TONEWORD._INDEX + " = " + index,
			null, null, null, null);
		c.moveToFirst();
		return c;
	}
	
	private void importTone() {
		LogDog.i(TAG, "importTone");
		try {
			InputStream is = SongLyricApp.instance().getAssets().open("tones.txt");
			BufferedReader reader = new BufferedReader(new InputStreamReader(is));
			String s;
			SQLiteDatabase db = getWritableDatabase();
			int index = 0;
			String title = null;
			int type = 0;
			String subject;
			String tone_p = SongLyricApp.instance().getResources().getString(R.string.tone_p);
			String tone_z = SongLyricApp.instance().getResources().getString(R.string.tone_z);
			String tone_r = SongLyricApp.instance().getResources().getString(R.string.tone_r);
			while((s = reader.readLine()) != null) {
				if(s.isEmpty())
					continue;
				String[] values = s.split(",");
				if(values.length == 2) { // title line
					index = Integer.parseInt(values[0]);
					title = values[1];
				} else if(values.length == 3) { // subject line
					type = values[1].equals(tone_p) ? TONE_TYPE_P :
						values[1].equals(tone_z) ? TONE_TYPE_Z :
						values[1].equals(tone_r) ? TONE_TYPE_R : 0;
					subject = values[2];
					ContentValues cv = new ContentValues();
					cv.put(TONE._INDEX, index);
					cv.put(TONE._TITLE, title);
					cv.put(TONE._TYPE, type);
					cv.put(TONE._SUBJECT, subject);
					db.insert(TONE._T,  null, cv);
					LogDog.i(TAG, "new tone:" + index + "/" + title + "/" + type + "/" + subject);
				} else if(values.length == 4) { // word line
					ContentValues cv = new ContentValues();
					cv.put(TONEWORD._INDEX, index);
					cv.put(TONEWORD._TYPE, type);
					cv.put(TONEWORD._SUBJECT, values[2]);
					cv.put(TONEWORD._WORDS, values[3]);
					db.insert(TONEWORD._T, null, cv);
				}
			}
			reader.close();
		} catch (IOException e) {
			LogDog.e(TAG, "Failed to import tones.txt because " + e.getMessage());
		}
	}
	
	// INITIALIZE
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		String s = "CREATE TABLE " + TONE._T + " ( "
			+ TONE._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ TONE._INDEX + " INTEGER, "
			+ TONE._TITLE + " TEXT, "
			+ TONE._TYPE + " INTEGER, "
			+ TONE._SUBJECT + " TEXT ) ";
		LogDog.i(TAG, s);
		db.execSQL(s);
		s = "CREATE TABLE " + TONEWORD._T + " ( "
			+ TONEWORD._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ TONEWORD._INDEX + " INTEGER, "
			+ TONEWORD._SUBJECT + " TEXT, "
			+ TONEWORD._TYPE + " INTEGER, "
			+ TONEWORD._WORDS + " TEXT ) ";
		LogDog.i(TAG, s);
		db.execSQL(s);
		s = "CREATE TABLE " + MUSIC._T + " ( "
			+ MUSIC._ID + " INTEGER PRIMARY KEY, "
			+ MUSIC._NAME + " TEXT, "
			+ MUSIC._WORDS + " INTEGER, "
			+ MUSIC._LINES1 + " INTEGER, "
			+ MUSIC._LINES2 + " INTEGER, "
			+ MUSIC._SIZE + " TEXT, "
			+ MUSIC._TONE_TYPE + " INTEGER, "
			+ MUSIC._FAVORIT + " INTEGER DEFAULT 0 ) ";
		LogDog.i(TAG, s);
		db.execSQL(s);
		s = "CREATE TABLE " + MUSICLINE._T + " ( "
			+ MUSICLINE._MUSIC + " INTEGER, "
			+ MUSICLINE._LINE + " INTEGER, "
			+ MUSICLINE._WORDS + " TEXT ) ";
		LogDog.i(TAG, s);
		db.execSQL(s);
		s = "CREATE TABLE " + LYRIC._T + " ( "
			+ LYRIC._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ LYRIC._MUSIC + " INTEGER, "
			+ LYRIC._DYNASTY + " TEXT, "
			+ LYRIC._AUTHOR + " TEXT, "
			+ LYRIC._TITLE + " TEXT, "
			+ LYRIC._TONE + " INTEGER, "
			+ LYRIC._FAVORIT + " INTEGER DEFAULT 0 ) ";
		LogDog.i(TAG, s);
		db.execSQL(s);
		s = "CREATE TABLE " + LYRICLINE._T + " ( "
			+ LYRICLINE._LYRIC + " INTEGER, "
			+ LYRICLINE._LINE + " TEXT, "
			+ LYRICLINE._WORDS + " TEXT ) ";
		LogDog.i(TAG, s);
		db.execSQL(s);
		_isEmpty = true;
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		LogDog.i(TAG, "Upgrade from " + oldVersion + " to " + newVersion);
		String s = "DROP TABLE IF EXISTS " + MUSIC._T;
		LogDog.i(TAG, s);
		db.execSQL(s);
		s = "DROP TABLE IF EXISTS " + MUSICLINE._T;
		LogDog.i(TAG, s);
		db.execSQL(s);
		s = "DROP TABLE IF EXISTS " + LYRIC._T;
		LogDog.i(TAG, s);
		db.execSQL(s);
		s = "DROP TABLE IF EXISTS " + LYRICLINE._T;
		LogDog.i(TAG, s);
		db.execSQL(s);
		s = "DROP TABLE IF EXISTS " + TONE._T;
		LogDog.i(TAG, s);
		db.execSQL(s);
		s = "DROP TABLE IF EXISTS " + TONEWORD._T;
		LogDog.i(TAG, s);
		db.execSQL(s);
		onCreate(db);
	}
	
	public void copyOut() {
		try {
			SQLiteDatabase db = _instance.getReadableDatabase();
			db.close();
			if (!Environment.getExternalStorageState().equals(Environment.MEDIA_MOUNTED)) {
				Log.w(TAG, "sdcard unmounted,skip dump exception");
				return;
			}
			String file = SongLyricApp.instance().getDatabasePath(TAG).getAbsolutePath();
			InputStream is = new FileInputStream(file);
			String PATH = Environment.getExternalStorageDirectory().getPath() + "/log";
			File dir = new File(PATH);
			if (!dir.exists()) {
				dir.mkdirs();
			}
			File file2 = new File(PATH + "/" + TAG);
			OutputStream os = new FileOutputStream(file2);
			byte[] buffer = new byte[1024];
			int length;
			while ((length = is.read(buffer))>0) {
				os.write(buffer, 0, length);
			}
			os.flush();
			os.close();
			is.close();
		}
		catch(IOException e) {
			LogDog.i(TAG, "failed to copy out " + _version);
		}
	}

}
